﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Dropthings.DAL
{
    public class UP_GetPagedMember : BaseProcedure
    {
        public UP_GetPagedMember(int startRowIndex, int maximumRows)
            : base()
        {
            _StoredProcedureName = "TurnPage";
            _Parameters=new SqlParameter[9];
            _Parameters[0] = new SqlParameter("@TableName", SqlDbType.NVarChar, 1000);
            _Parameters[0].Direction = ParameterDirection.Input;
            _Parameters[0].Value = "aspnet_Membership";
            _Parameters[1] = new SqlParameter("@ReturnFields", SqlDbType.NVarChar, 1000);
            _Parameters[1].Direction = ParameterDirection.Input;
            _Parameters[1].Value = "*";
            _Parameters[2] = new SqlParameter("@PageSize", SqlDbType.Int, 4);
            _Parameters[2].Direction = ParameterDirection.Input;
            _Parameters[2].Value = maximumRows;
            _Parameters[3] = new SqlParameter("@PageIndex", SqlDbType.Int, 4);
            _Parameters[3].Direction = ParameterDirection.Input;
            _Parameters[3].Value =startRowIndex;
            _Parameters[4] = new SqlParameter("@Where", SqlDbType.NVarChar, 1000);
            _Parameters[4].Direction = ParameterDirection.Input;
            _Parameters[4].Value = string.Empty;
            _Parameters[5] = new SqlParameter("@Orderfld", SqlDbType.NVarChar, 100);
            _Parameters[5].Direction = ParameterDirection.Input;
            _Parameters[5].Value = "CreateDate";
            _Parameters[6] = new SqlParameter("@OrderType", SqlDbType.Int, 4);
            _Parameters[6].Direction = ParameterDirection.Input;
            _Parameters[6].Value = 0;
            _Parameters[7] = new SqlParameter("@PageCount", SqlDbType.BigInt, 8);
            _Parameters[7].Direction = ParameterDirection.Output;
            _Parameters[8] = new SqlParameter("@RecordCount", SqlDbType.BigInt, 8);
            _Parameters[8].Direction = ParameterDirection.Output;
        }

        public int PageCount { get { return Convert.ToInt32(_Parameters[7].Value); } }
        public int RecordCunt { get { return Convert.ToInt32(_Parameters[8].Value); } }
    }

    public class UQ_GetMemberCount : BaseQuery
    {
        public UQ_GetMemberCount()
            : base()
        {
            _QueryStringName = "select count(*) from aspnet_Membership";
        }
    }

    public class UQ_GetPagedMember : BaseQuery
    {
        public UQ_GetPagedMember()
            : base()
        {
            _QueryStringName = "select a.UserId,b.UserName,a.IsApproved from aspnet_Membership a left join aspnet_Users b on a.UserId=b.UserId order by a.CreateDate desc";
        }
    }

    public class UQ_DeleteUserRoles : BaseQuery
    {
        public UQ_DeleteUserRoles(Guid userGuid)
            : base()
        {
            _QueryStringName = string.Format("delete from aspnet_UsersInRoles where UserId='{0}' and UPPER(RoleId) <>'4E7C1E10-4E20-43A0-BFD8-93B2CAC372E3'", userGuid); 
        }
    }

    public class UQ_GetUserNamedFromUserGuid : BaseQuery
    {
        public UQ_GetUserNamedFromUserGuid(Guid userGuid)
            : base()
        {
            _QueryStringName = string.Format("select UserName from aspnet_Users where UserId='{0}'",userGuid);
        }
    }

    public class UQ_GetOrganizeBaseInfo : BaseQuery
    {
        public UQ_GetOrganizeBaseInfo()
            : base()
        {
            _QueryStringName = "select OrganizeName,OrganizeIdentify from DB_OrganizeBaseInfo";
        }
    }

	public class UQ_GetAmmeterECYearDatas : BaseQuery
    {
        public UQ_GetAmmeterECYearDatas(string identifyCode, int year)
            : base()
        {
            if (identifyCode == "-1")
            {
                _QueryStringName = string.Format("SELECT ReadingMonth,ISNULL(dbo.fun_getStandardCoalValue('01',SUM(MonthData)),0) MonthData FROM (SELECT ReadingMonth,MonthData FROM DB_AmmeterMonthDatas where readingYear={0})a group by ReadingMonth order by ReadingMonth",
                    year);
            }
            else
            {
                _QueryStringName = string.Format("SELECT ReadingMonth,ISNULL(dbo.fun_getStandardCoalValue('01',SUM(MonthData)),0) MonthData FROM (SELECT ReadingMonth,MonthData FROM DB_AmmeterMonthDatas where IdentifyCode='{0}' and readingYear={1})a group by ReadingMonth order by ReadingMonth",
                    identifyCode, year);
            }
        }
    }

    public class UQ_GetGasECYearDatas : BaseQuery
    {
        public UQ_GetGasECYearDatas(string identifyCode, int year)
            : base()
        {
            if (identifyCode == "-1")
            {
                _QueryStringName = string.Format("SELECT ReadingMonth,ISNULL(dbo.fun_getStandardCoalValue('03',SUM(MonthData)),0) MonthData FROM (SELECT ReadingMonth,MonthData FROM DB_GasMonthDatas where readingYear={0})a group by ReadingMonth order by ReadingMonth",
                    year);
            }
            else
            {
                _QueryStringName = string.Format("SELECT ReadingMonth,ISNULL(dbo.fun_getStandardCoalValue('03',SUM(MonthData)),0) MonthData FROM (SELECT ReadingMonth,MonthData FROM DB_GasMonthDatas where IdentifyCode='{0}' and readingYear={1})a group by ReadingMonth order by ReadingMonth",
                    identifyCode, year);
            }
        }
    }

    public class UQ_GetAmmeterMonthDatas : BaseQuery
    {
        public UQ_GetAmmeterMonthDatas(string identifyCode, int year, string energyTypeNo = "01")
            : base()
        {
            if (identifyCode == "-1")
            {
                _QueryStringName = string.Format("SELECT ReadingMonth,ISNULL(dbo.fun_getStandardCoalValue('{0}',SUM(MonthData)),0) MonthData  FROM (SELECT ReadingMonth,MonthData FROM DB_AmmeterMonthDatas where  EnergyTypeNo='{0}' and readingYear={1})a group by ReadingMonth order by ReadingMonth",
                    energyTypeNo, year);
            }
            else
            {
                _QueryStringName = string.Format("SELECT ReadingMonth,ISNULL(dbo.fun_getStandardCoalValue('{1}',SUM(MonthData)),0) MonthData  FROM (SELECT ReadingMonth,MonthData FROM DB_AmmeterMonthDatas where IdentifyCode='{0}' and EnergyTypeNo='{1}' and readingYear={2})a group by ReadingMonth order by ReadingMonth",
                    identifyCode, energyTypeNo, year);
            }
        }
    }

    public class UQ_GetAmmeterDayDatas : BaseQuery
    {
        public UQ_GetAmmeterDayDatas(string identifyCode, int year, int month, string energyTypeNo = "01")
            : base()
        {
            if (identifyCode == "-1")
            {
                _QueryStringName = string.Format("SELECT ReadingDay,ISNULL(dbo.fun_getStandardCoalValue('{0}',SUM(DayTotalData)),0) DayTotalData  FROM (SELECT ReadingDay,DayTotalData FROM DB_AmmeterDayDatas where EnergyTypeNo='{0}' and readingYear={1} and ReadingMonth={2})a group by ReadingDay order by ReadingDay",
                   energyTypeNo, year, month);
            }
            else
            {
                _QueryStringName = string.Format("SELECT ReadingDay,ISNULL(dbo.fun_getStandardCoalValue('{1}',SUM(DayTotalData)),0) DayTotalData  FROM (SELECT ReadingDay,DayTotalData FROM DB_AmmeterDayDatas where IdentifyCode='{0}' and EnergyTypeNo='{1}' and readingYear={2} and ReadingMonth={3})a group by ReadingDay order by ReadingDay",
                    identifyCode, energyTypeNo, year, month);
            }
        }
    }

    public class UQ_GetAmmeterTimeDatas : BaseQuery
    {
        public UQ_GetAmmeterTimeDatas(string identifyCode, int year, int month, int day, string energyTypeNo = "01")
            : base()
        {
            if (identifyCode == "-1")
            {
                _QueryStringName = string.Format("SELECT ReadingTime,ISNULL(dbo.fun_getStandardCoalValue('{0}',SUM(TimeTotalData)),0) TimeTotalData  FROM (SELECT ReadingTime,TimeTotalData FROM DB_AmmeterTimeDatas where EnergyTypeNo='{0}' and readingYear={1} and ReadingMonth={2} and ReadingDay={3})a group by ReadingTime order by ReadingTime",
                    energyTypeNo, year, month, day);
            }
            else
            {
                _QueryStringName = string.Format("SELECT ReadingTime,ISNULL(dbo.fun_getStandardCoalValue('{1}',SUM(TimeTotalData)),0) TimeTotalData  FROM (SELECT ReadingTime,TimeTotalData FROM DB_AmmeterTimeDatas where IdentifyCode='{0}' and EnergyTypeNo='{1}' and readingYear={2} and ReadingMonth={3} and ReadingDay={4})a group by ReadingTime order by ReadingTime",
                    identifyCode, energyTypeNo, year, month, day);
            }
        }
    }

    public class UQ_GetGasMonthDatas : BaseQuery
    {
        public UQ_GetGasMonthDatas(string identifyCode, int year, string energyTypeNo = "03")
            : base()
        {
            if (identifyCode == "-1")
            {
                _QueryStringName = string.Format("SELECT ReadingMonth,ISNULL(dbo.fun_getStandardCoalValue('{0}',SUM(MonthData)),0) MonthData  FROM (SELECT ReadingMonth,MonthData FROM DB_GasMonthDatas where  EnergyTypeNo='{0}' and readingYear={1})a group by ReadingMonth order by ReadingMonth",
                    energyTypeNo, year);
            }
            else
            {
                _QueryStringName = string.Format("SELECT ReadingMonth,ISNULL(dbo.fun_getStandardCoalValue('{1}',SUM(MonthData)),0) MonthData  FROM (SELECT ReadingMonth,MonthData FROM DB_GasMonthDatas where IdentifyCode='{0}' and EnergyTypeNo='{1}' and readingYear={2})a group by ReadingMonth order by ReadingMonth",
                    identifyCode, energyTypeNo, year);
            }
        }
    }

    public class UQ_GetGasDayDatas : BaseQuery
    {
        public UQ_GetGasDayDatas(string identifyCode, int year, int month, string energyTypeNo = "03")
            : base()
        {
            if (identifyCode == "-1")
            {
                _QueryStringName = string.Format("SELECT ReadingDay,ISNULL(dbo.fun_getStandardCoalValue('{0}',SUM(DayTotalData)),0) DayTotalData  FROM (SELECT ReadingDay,DayTotalData FROM DB_GasDayDatas where EnergyTypeNo='{0}' and readingYear={1} and ReadingMonth={2})a  group by ReadingDay order by ReadingDay",
                   energyTypeNo, year, month);
            }
            else
            {
                _QueryStringName = string.Format("SELECT ReadingDay,ISNULL(dbo.fun_getStandardCoalValue('{1}',SUM(DayTotalData)),0) DayTotalData  FROM (SELECT ReadingDay,DayTotalData FROM DB_GasDayDatas where IdentifyCode='{0}' and EnergyTypeNo='{1}' and readingYear={2} and ReadingMonth={3})a  group by ReadingDay order by ReadingDay",
                    identifyCode, energyTypeNo, year, month);
            }
        }
    }

    public class UQ_GetGasTimeDatas : BaseQuery
    {
        public UQ_GetGasTimeDatas(string identifyCode, int year, int month, int day, string energyTypeNo = "03")
            : base()
        {
            if (identifyCode == "-1")
            {
                _QueryStringName = string.Format("SELECT ReadingTime,ISNULL(dbo.fun_getStandardCoalValue('{0}',SUM(TimeTotalData)),0) TimeTotalData  FROM (select ReadingTime,TimeTotalData FROM DB_GasTimeDatas where EnergyTypeNo='{0}' and readingYear={1} and ReadingMonth={2} and ReadingDay={3})a group by ReadingTime order by ReadingTime",
                    energyTypeNo, year, month, day);
            }
            else
            {
                _QueryStringName = string.Format("SELECT ReadingTime,ISNULL(dbo.fun_getStandardCoalValue('{1}',SUM(TimeTotalData)),0) TimeTotalData  FROM (select ReadingTime,TimeTotalData FROM DB_GasTimeDatas where IdentifyCode='{0}' and EnergyTypeNo='{1}' and readingYear={2} and ReadingMonth={3} and ReadingDay={4})a group by ReadingTime order by ReadingTime",
                    identifyCode, energyTypeNo, year, month, day);
            }
        }
    }

    public class UQ_GetAmmeterRankByPeople : BaseQuery
    {
        public UQ_GetAmmeterRankByPeople(int year,int month)
            : base()
        {
            if (month == -1)
            {
                _QueryStringName = string.Format("select a.IdentifyCode,d.OrganizeName,a.MonthData/d.UserNum MonthData from " +
                    " (select IdentifyCode,isnull(SUM(MonthData),0) MonthData from DB_AmmeterMonthDatas " +
                    " where ReadingYear={0} and EnergyTypeNo='01' group by IdentifyCode)a left join " +
                    " (select c.OrganizeIdentify,b.UserNum,c.OrganizeName from DB_OrganizeBaseInfo c left join DB_OrganizeExInfo  b on c.OrganizeID=b.OrganizeID) d " +
                    " on a.IdentifyCode=d.OrganizeIdentify order by  MonthData desc", year);
            }
            else
            {
                _QueryStringName = string.Format("select a.IdentifyCode,d.OrganizeName,a.MonthData/d.UserNum MonthData from " +
                    " (select IdentifyCode,isnull(SUM(MonthData),0) MonthData from DB_AmmeterMonthDatas " +
                    " where ReadingYear={0} and EnergyTypeNo='01' and ReadingMonth={1} group by IdentifyCode)a left join " +
                    " (select c.OrganizeIdentify,b.UserNum,c.OrganizeName from DB_OrganizeBaseInfo c left join DB_OrganizeExInfo  b on c.OrganizeID=b.OrganizeID) d " +
                    " on a.IdentifyCode=d.OrganizeIdentify order by MonthData desc ", year, month);
            }
        }
    }

    public class UQ_GetWaterRankByPeople : BaseQuery
    {
        public UQ_GetWaterRankByPeople(int year, int month)
            : base()
        {
            if (month == -1)
            {
                _QueryStringName = string.Format("select a.IdentifyCode,d.OrganizeName,a.MonthData/d.UserNum MonthData from " +
                    " (select IdentifyCode,isnull(SUM(MonthData),0) MonthData from DB_WaterMonthDatas " +
                    " where ReadingYear={0} group by IdentifyCode)a left join " +
                    " (select c.OrganizeIdentify,b.UserNum,c.OrganizeName from DB_OrganizeBaseInfo c left join DB_OrganizeExInfo  b on c.OrganizeID=b.OrganizeID) d " +
                    " on a.IdentifyCode=d.OrganizeIdentify order by MonthData desc ", year);
            }
            else
            {
                _QueryStringName = string.Format("select a.IdentifyCode,d.OrganizeName,a.MonthData/d.UserNum MonthData from " +
                    " (select IdentifyCode,isnull(SUM(MonthData),0) MonthData from DB_WaterMonthDatas " +
                    " where ReadingYear={0} and ReadingMonth={1} group by IdentifyCode)a left join " +
                    " (select c.OrganizeIdentify,b.UserNum,c.OrganizeName from DB_OrganizeBaseInfo c left join DB_OrganizeExInfo  b on c.OrganizeID=b.OrganizeID) d " +
                    " on a.IdentifyCode=d.OrganizeIdentify order by MonthData desc ", year, month);
            }
        }
    }

    public class UQ_GetAmmeterRankByBuildingArea : BaseQuery
    {
        public UQ_GetAmmeterRankByBuildingArea(int year, int month)
            : base()
        {
            if (month == -1)
            {
                _QueryStringName = string.Format("select a.IdentifyCode,d.OrganizeName,a.MonthData/d.BuildingArea MonthData from " +
                    " (select IdentifyCode,isnull(SUM(MonthData),0) MonthData from DB_AmmeterMonthDatas " +
                    " where ReadingYear={0} and EnergyTypeNo='01' group by IdentifyCode)a left join " +
                    " (select c.OrganizeIdentify,b.BuildingArea,c.OrganizeName from DB_OrganizeBaseInfo c left join DB_OrganizeExInfo  b on c.OrganizeID=b.OrganizeID) d " +
                    " on a.IdentifyCode=d.OrganizeIdentify order by MonthData desc ", year);
            }
            else
            {
                _QueryStringName = string.Format("select a.IdentifyCode,d.OrganizeName,a.MonthData/d.BuildingArea MonthData from " +
                    " (select IdentifyCode,isnull(SUM(MonthData),0) MonthData from DB_AmmeterMonthDatas " +
                    " where ReadingYear={0} and EnergyTypeNo='01' and ReadingMonth={1} group by IdentifyCode)a left join " +
                    " (select c.OrganizeIdentify,b.BuildingArea,c.OrganizeName from DB_OrganizeBaseInfo c left join DB_OrganizeExInfo  b on c.OrganizeID=b.OrganizeID) d " +
                    " on a.IdentifyCode=d.OrganizeIdentify order by MonthData desc ", year, month);
            }
        }
    }

    public class UQ_AddUserOrganizeIdentify : BaseQuery
    {
        public UQ_AddUserOrganizeIdentify(Guid userGuid, string organizeIdentify)
            : base()
        {
            _QueryStringName = string.Format("insert into UT_UserOrganizeIdentify(UID,OrganizeIdentify) values('{0}','{1}')",
                userGuid.ToString(), organizeIdentify);
        }
    }

    public class UQ_UpdateUserOrganizeIdentify : BaseQuery
    {
        public UQ_UpdateUserOrganizeIdentify(Guid userGuid, string organizeIdentify)
            : base()
        {
            _QueryStringName = string.Format("update UT_UserOrganizeIdentify set OrganizeIdentify='{0}' where UID='{1}'",
                organizeIdentify,userGuid.ToString());
        }
    }

    public class UQ_DeleteUserOrganizeIdentify : BaseQuery
    {
        public UQ_DeleteUserOrganizeIdentify(Guid userGuid)
            : base()
        {
            _QueryStringName = string.Format("delete UT_UserOrganizeIdentify where UID='{0}'",
                userGuid.ToString());
        }
    }

    public class UQ_GetUserOrganizeIdentify : BaseQuery
    {
        public UQ_GetUserOrganizeIdentify(Guid userGuid)
            : base()
        {
            _QueryStringName = string.Format("select OrganizeIdentify from UT_UserOrganizeIdentify where UID='{0}'",
                userGuid.ToString());
        }
    }

    public class UQ_ListUserOrganizeIdentify : BaseQuery
    {
        public UQ_ListUserOrganizeIdentify()
            : base()
        {
            _QueryStringName = "select c.UserName,c.UID,c.OrganizeIdentify,d.OrganizeName from "+
                "(select a.UID,a.OrganizeIdentify,b.UserName from UT_UserOrganizeIdentify a left join aspnet_Users b on a.UID=b.UserId ) c "+
                " left join DB_OrganizeBaseInfo d on c.OrganizeIdentify=d.OrganizeIdentify ";
        }
    }

    public class UQ_ListAmmerterByXun : BaseQuery
    {
        public UQ_ListAmmerterByXun(int year, int month,string identifyCode)
            : base()
        {
            _QueryStringName = string.Format("select case (ReadingDay-1)/10 when 0 then 'A' when 1 then 'B' else 'C' end as ReadingMonth,isnull(SUM(DayTotalData),0) as AmmeterMonthData," +
                " dbo.fun_getStandardCoalValue('01',isnull(SUM(DayTotalData),0)) as AmmeterCoal,isnull(SUM(AmmeterPrice*DayTotalData),0) as AmmeterMoney from DB_AmmeterDayDatas " +
                " where ReadingYear={0} and ReadingMonth={1} and IdentifyCode='{2}' group by case (ReadingDay-1)/10 when 0 then 'A' when 1 then 'B' else 'C' end order by ReadingMonth", year, month, identifyCode);
        }
    }

    public class UQ_ListWaterByXun : BaseQuery
    {
        public UQ_ListWaterByXun(int year, int month, string identifyCode)
            : base()
        {
            _QueryStringName = string.Format("select case (ReadingDay-1)/10 when 0 then 'A' when 1 then 'B' else 'C' end as ReadingMonth,isnull(SUM(DayTotalData),0) as WaterMonthData, " +
                " isnull(SUM(WaterPrice*DayTotalData),0) as WaterMoney  from DB_WaterDayDatas " +
                " where ReadingYear={0} and ReadingMonth={1} and IdentifyCode='{2}' group by case (ReadingDay-1)/10 when 0 then 'A' when 1 then 'B' else 'C' end order by ReadingMonth",year,month,identifyCode);
        }
    }

    public class UQ_ListGasByXun : BaseQuery
    {
        public UQ_ListGasByXun(int year, int month, string identifyCode)
            : base()
        {
            _QueryStringName = string.Format("select case (ReadingDay-1)/10 when 0 then 'A' when 1 then 'B' else 'C' end as ReadingMonth,isnull(SUM(DayTotalData),0) as GasMonthData," +
                " dbo.fun_getStandardCoalValue('03',isnull(SUM(DayTotalData),0)) as GasCoal,isnull(SUM(GasPrice*DayTotalData),0) as GasMoney  from DB_GasDayDatas " +
                " where ReadingYear={0} and ReadingMonth={1} and IdentifyCode='{2}' group by case (ReadingDay-1)/10 when 0 then 'A' when 1 then 'B' else 'C' end order by ReadingMonth", year, month, identifyCode);
        }
    }

    public class UQ_GetThreeMonthWithAmmeterGas : BaseQuery
    {
        public UQ_GetThreeMonthWithAmmeterGas(int year, int month, string identifyCode)
            : base()
        {
            _QueryStringName = string.Format("select (select ISNULL(dbo.fun_getStandardCoalValue('01',SUM(MonthData)),0)as AmmeterCoal from DB_AmmeterMonthDatas " +
                " where IdentifyCode='{0}' and ReadingYear={1} and ReadingMonth={2})AmmeterCoal," +
                "(select ISNULL(dbo.fun_getStandardCoalValue('03',SUM(MonthData)),0)as GasCoal from DB_GasMonthDatas " +
                " where IdentifyCode='{0}' and ReadingYear={1} and ReadingMonth={2})GasCoal ", identifyCode, year, month);
        }
    }

    public class UQ_GetCurrentMonthMoney : BaseQuery
    {
        public UQ_GetCurrentMonthMoney(int year, int month, string identifyCode)
            : base()
        {
            _QueryStringName = string.Format("select '电' as Category ,ISNULL(SUM(MonthMoney),0) as MonthMoney from DB_AmmeterMonthDatas where IdentifyCode='{0}' and ReadingYear={1} and ReadingMonth={2} and EnergyTypeNo='01' " +
                " union select '水' ,ISNULL(SUM(MonthMoney),0) from DB_WaterMonthDatas where IdentifyCode='{0}' and ReadingYear={1} and ReadingMonth={2} and EnergyTypeNo='02' " +
                " union select '气' ,ISNULL(SUM(MonthMoney),0) from DB_GasMonthDatas where IdentifyCode='{0}' and ReadingYear={1} and ReadingMonth={2} and EnergyTypeNo='03'", identifyCode, year, month);
        }
    }
}
